﻿using System;
using System.Data;
using System.Data.SqlClient;
using JiaBao.Core.Data;
using JiaBao.Core.Infrastructure;

namespace JiaBao.Services
{
    public static class SqlHelper
    {
        public static DataSettings Setting = EngineContext.Current.Resolve<DataSettings>();


        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameter)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(Setting.DataConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(parameter);
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 执行sql  获取DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameter)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(Setting.DataConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(parameter);
                        DataTable table = new DataTable();
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        da.Fill(table);
                        return table;
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public static object ExecuteScalar(string sql, params SqlParameter[] parameter)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(Setting.DataConnectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(parameter);
                        return cmd.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }


        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns></returns>
        public static bool Test()
        {
            using (SqlConnection conn = new SqlConnection(Setting.DataConnectionString))
            {
                try
                {
                    conn.Open();
                    return true;
                }
                catch (Exception)
                {

                    return false;
                }

            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static int StoredProcedureNonQuery(string sql, params SqlParameter[] parameter)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(Setting.DataConnectionString))
                {

                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandTimeout = 120000;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = sql;
                        cmd.Parameters.AddRange(parameter);
                        return cmd.ExecuteNonQuery();

                    }
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
    }
}
